"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.
"""

"""
Case Type   : 资源池化，行访问控制策略
Case Name   : 资源池化场景下,定义访问控制策略,行访问控制影响的SQL操作为select（unlogged表）
Create At   : 2025-03-03
Owner       : zhongchengzhi
Description :
    1、查询集群状态
    2、创建unlogged表
    3、创建用户
    4、插入数据
    5、授予用户表的select权限并打开行级安全检查
    6、构造主机failover
    7、创建行访问控制策略
    8、以test_user_001登录数据库，测试,查询表信息
    9、修改表信息
    10、删除表信息
    11、清理环境
    12、恢复集群初始状态

Expect      :
    1、集群状态正常
    2、创建成功
    3、创建成功
    4、插入成功
    5、赋予权限成功
    6、failover成功
    7、创建行访问控制策略成功
    8、只有usr字段值为test_user_001才对用户test_user_001可见
    9、合理报错
    10、合理报错
    11、清理环境成功
    12、恢复集群初始状态成功
History     :
    1、2025-03-03 创建
    2、2025-07-12 改造用例为1p1s环境可执行
"""
import os
import unittest
import time

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common
from testcase.utils.Logger import Logger
from yat.test import macro
from yat.test import Node

Primary_ROOTSH = CommonSH('PrimaryRoot')
Primary_SH = CommonSH('PrimaryDbUser')
Standby1_SH = CommonSH('Standby1DbUser')

@unittest.skipIf(1 == Primary_SH.get_node_num(), '单机环境不执行')
class SharedStorage(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.common = Common()
        self.s1_comsh = CommonSH('Standby1DbUser')

    def test_shared_storage(self):
        text = '-----step1:查询数据库集群状态-----'
        self.log.info(text)
        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, '执行失败' + text)

        text = '-----step2:创建unlogged表;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop table if exists test_policy5 cascade;
                create unlogged table test_policy5(id int,usr name);
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('CREATE TABLE', status, '执行失败' + text)

        text = '-----step3:创建测试用户;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop user if exists test_user_001 cascade;
                create user test_user_001 password 'huawei@123';
                drop user if exists test_user_002 cascade;
                create user test_user_002 password 'huawei@123';
                drop user if exists test_user_003 cascade;
                create user test_user_003 password 'huawei@123';
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('CREATE ROLE', status, '执行失败' + text)

        text = '-----step4:向表中插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''
                insert into test_policy5(id, usr) values(1, 'test_user_001');
                insert into test_policy5(id, usr) values(2, 'test_user_002');
                insert into test_policy5(id, usr) values(3, 'test_user_002');
                insert into test_policy5(id, usr) values(4, 'test_user_002');
                insert into test_policy5(id, usr) values(5, 'test_user_001');
                insert into test_policy5(id, usr) values(5, 'test_user_003');
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('INSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1', status, '执行失败' + text)

        text = '-----step5:授予用户表的select权限并打开行级安全检查 expect:成功-----'
        self.log.info(text)
        sql = f'''
                grant select on test_policy5 to test_user_001,test_user_002,test_user_003;
                ALTER TABLE test_policy5 ENABLE ROW LEVEL SECURITY;
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('GRANT\nALTER TABLE', status, '执行失败' + text)

        text = '-----step6:构造主机DN故障 expect:集群failover成功-----'
        self.log.info(text)
        stopdn = Primary_SH.exec_failover()
        self.log.info(stopdn)
        time.sleep(40)
        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        res1 = status.splitlines()[-1].split('|')[0].split('6001')[-1]
        res2 = status.splitlines()[-1].split('|')[1].split('6002')[-1]
        self.log.info(res1)
        self.log.info(res2)
        self.assertIn('P Down    Manually stopped', res1, '执行失败' + text)
        self.assertTrue('S Primary Normal' in res2, '执行失败' + text)

        text = '-----step7:创建策略,指定行访问控制影响的数据库用户为public;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop POLICY if exists t_pol1 ON test_policy5;
                CREATE POLICY t_pol1 ON test_policy5 FOR SELECT TO PUBLIC USING (usr = current_user);
                '''
        self.log.info(sql)
        status = self.s1_comsh.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('CREATE ROW LEVEL SECURITY POLICY', status, '执行失败' + text)

        text = '-----step8:以test_user_001登录数据库，测试,查询表信息;expect:查询结果-----'
        self.log.info(text)
        exe_cmd = f'''
                    gsql -U test_user_001 -W huawei@123 -c "select * from test_policy5 where usr = 'test_user_001';"
                    '''
        self.log.info(exe_cmd)
        res = Standby1_SH.exec_cmd_under_user(exe_cmd)
        self.log.info(res)
        self.assertIn('(0 rows)', res, '执行失败' + text)

        text = '-----step9:修改表信息;expect:合理报错-----'
        self.log.info(text)
        exe_cmd = f'''
                    gsql -U test_user_001 -W huawei@123 -c "update test_policy5 set id = id + 50 where usr = 'test_user_001';"
                    '''
        self.log.info(exe_cmd)
        res = Standby1_SH.exec_cmd_under_user(exe_cmd)
        self.log.info(res)
        self.assertIn('ERROR:  permission denied for relation test_policy5', res, '执行失败' + text)


        text = '-----step10:删除表信息;expect:合理报错-----'
        self.log.info(text)
        exe_cmd = f'''
                    gsql -U test_user_001 -W huawei@123 -c "delete from test_policy5 where usr = 'test_user_001';"
                    '''
        self.log.info(exe_cmd)
        res = Standby1_SH.exec_cmd_under_user(exe_cmd)
        self.log.info(res)
        self.assertIn('ERROR:  permission denied for relation test_policy5', res, '执行失败' + text)

        text = '-----step11:清理环境;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop POLICY if exists t_pol1 ON test_policy5;
                drop table test_policy5;
                drop user if exists test_user_001 cascade;
                drop user if exists test_user_002 cascade;
                drop user if exists test_user_003 cascade;
                '''
        self.log.info(sql)
        status = Standby1_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('DROP TABLE', status, '执行失败' + text)

    def tearDown(self):
        text = '-----step12:恢复环境为初始集群状态; expect:成功-----'
        self.log.info(text)
        status = Primary_SH.exec_cm_ctl(mode='stop')
        self.log.info(status)
        self.assertIn('cm_ctl: stop cluster successfully.', status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='start')
        self.log.info(status)
        self.assertIn('cm_ctl: start cluster successfully.', status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='switchover', param='-a')
        self.log.info(status)
        self.assertIn(self.constant.cm_switchover_success_msg, status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')